﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Clinica_Frba.Listados_Estadisticos.queries
{
    class EspecialidadesConMasBonosDeFarmaciaRecetados : Query
    {
        private static string EspecialidadesConMasBonosDeFarmaciaRecetadosQuery = "SELECT TOP 5 * FROM USUARIO;";

        public EspecialidadesConMasBonosDeFarmaciaRecetados()
            : base
                ("Especialidades con mas bonos de farmacia recetados", EspecialidadesConMasBonosDeFarmaciaRecetadosQuery, null)
        {
        }

        override public String SqlQuery(int semestre, int year)
        {
            if (semestre == 1)
            {
                return " select TOP 5 E.DESC_ESP Especialidad, SUM(CASE WHEN B.ID_BONO IS NOT NULL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) BETWEEN 1 AND 6 AND DATEPART ( YEAR , B.FECHA_PRESCRIPCION ) = " + year + " THEN 1 ELSE 0 END) Recetadas, " +
" SUM(CASE WHEN B.ID_BONO IS NOT NULL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 1 AND DATEPART ( YEAR , B.FECHA_PRESCRIPCION ) = " + year + " THEN 1 ELSE 0 END) as mes_1, " +
" SUM(CASE WHEN B.ID_BONO IS NOT NULL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 2 AND DATEPART ( YEAR , B.FECHA_PRESCRIPCION ) = " + year + " THEN 1 ELSE 0 END) as mes_2, " +
" SUM(CASE WHEN B.ID_BONO IS NOT NULL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 3 AND DATEPART ( YEAR , B.FECHA_PRESCRIPCION ) = " + year + " THEN 1 ELSE 0 END) as mes_3, " +
" SUM(CASE WHEN B.ID_BONO IS NOT NULL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 4 AND DATEPART ( YEAR , B.FECHA_PRESCRIPCION ) = " + year + " THEN 1 ELSE 0 END) as mes_4, " +
" SUM(CASE WHEN B.ID_BONO IS NOT NULL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 5 AND DATEPART ( YEAR , B.FECHA_PRESCRIPCION ) = " + year + " THEN 1 ELSE 0 END) as mes_5, " +
" SUM(CASE WHEN B.ID_BONO IS NOT NULL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 6 AND DATEPART ( YEAR , B.FECHA_PRESCRIPCION ) = " + year + " THEN 1 ELSE 0 END) as mes_6 " +
" from ESPECIALIDAD E LEFT JOIN PROFESIONAL_ESPECIALIDAD PE ON E.ID_ESP = PE.ID_ESP LEFT JOIN PROFESIONAL P ON PE.ID_PROF = P.ID_PROF LEFT JOIN RECETA R ON P.ID_PROF = R.ID_PROF LEFT JOIN BONO_FARMACIA B ON R.ID_RECETA = B.ID_RECETA " +
//" WHERE  " +
//" DATEPART ( YEAR , B.FECHA_PRESCRIPCION ) = " + year +
//" and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) <= 6 " +
" GROUP BY E.DESC_ESP " +
" ORDER BY Recetadas DESC ";
            }
            else
            {
                return " select TOP 5 E.DESC_ESP Especialidad, SUM(CASE WHEN B.ID_BONO IS NOT NULL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) BETWEEN 7 AND 12 AND DATEPART ( YEAR , B.FECHA_PRESCRIPCION ) = " + year + " THEN 1 ELSE 0 END) Recetadas, " +
" SUM(CASE WHEN B.ID_BONO IS NOT NULL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 7 AND DATEPART ( YEAR , B.FECHA_PRESCRIPCION ) = " + year + " THEN 1 ELSE 0 END) as mes_7, " +
" SUM(CASE WHEN B.ID_BONO IS NOT NULL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 8 AND DATEPART ( YEAR , B.FECHA_PRESCRIPCION ) = " + year + " THEN 1 ELSE 0 END) as mes_8, " +
" SUM(CASE WHEN B.ID_BONO IS NOT NULL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 9 AND DATEPART ( YEAR , B.FECHA_PRESCRIPCION ) = " + year + " THEN 1 ELSE 0 END) as mes_9, " +
" SUM(CASE WHEN B.ID_BONO IS NOT NULL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 10 AND DATEPART ( YEAR , B.FECHA_PRESCRIPCION ) = " + year + " THEN 1 ELSE 0 END) as mes_10, " +
" SUM(CASE WHEN B.ID_BONO IS NOT NULL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 11 AND DATEPART ( YEAR , B.FECHA_PRESCRIPCION ) = " + year + " THEN 1 ELSE 0 END) as mes_11, " +
" SUM(CASE WHEN B.ID_BONO IS NOT NULL and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) = 12 AND DATEPART ( YEAR , B.FECHA_PRESCRIPCION ) = " + year + " THEN 1 ELSE 0 END) as mes_12 " +
" from ESPECIALIDAD E LEFT JOIN PROFESIONAL_ESPECIALIDAD PE ON E.ID_ESP = PE.ID_ESP LEFT JOIN PROFESIONAL P ON PE.ID_PROF = P.ID_PROF LEFT JOIN RECETA R ON P.ID_PROF = R.ID_PROF LEFT JOIN BONO_FARMACIA B ON R.ID_RECETA = B.ID_RECETA " +
//" WHERE  " +
//"  DATEPART ( YEAR , B.FECHA_PRESCRIPCION ) = " + year +
//" and DATEPART ( MONTH , B.FECHA_PRESCRIPCION ) > 6 " +
" GROUP BY E.DESC_ESP " +
" ORDER BY Recetadas DESC ";
            }
        }


    }

}
